package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchMarketReportInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchMarketReportTwoInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotleMarketTwoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotleMarketTwoServiceImpl implements IGetTotleMarketTwoService {
    @Autowired
    private SqlMapper sqlMapper;

    public String getsqlDepartmentTwo(SearchMarketReportTwoInfo searchInfo, String classification, String condition, int i, List<SearchMarketReportTwoInfo> list1) {
        String getabbreviationsql = "select id, abbreviation,Simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if(dept==null){
            tableHead= ShiroKit.getUser().getAbbreviation();
        }else{
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select '"+classification+"' as department , " +
                " (select count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime() + "' and t.filing_time <='" + searchInfo.getEndTime() + "' and t.appointment_pay_service='"+classification+"' and t.outpatient_name='" + searchInfo.getArrivalStore() + "' group by t.id) t) AS filingNumber, " +
                " (select count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime() + "' and t.appointment_time <='" + searchInfo.getEndTime() + "' and t.appointment_pay_service='"+classification+"' and t.outpatient_name='" + searchInfo.getArrivalStore() + "' group by t.id) t) AS appointmentNumber," +
                " (select count(1) from (select t.id from reservations_infosc t left join "+tableName2+" t1 on t.id=t1.reservations_id where t.first_visit_date>='" + searchInfo.getStartTime() + " ' and t.first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + searchInfo.getArrivalStore() + "'  and t1.treatment_unit='"+classification+"' group by t.id) t) as customerxkNumber," +
                " (select count(1) from (select t1.id from  reservations_infosc t1 left join "+tableName2+" t on t1.id=t.reservations_id where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' and t.treatment_unit='"+classification+"' group by t1.id) b) as customerNumber, " +
                " (select count(1) from (select t1.id from reservations_infosc t1 left join "+tableName2+" t on t1.id=t.reservations_id where t.arrival_time <='" + searchInfo.getEndTime() + "' and t.isdeal_status='已成交' and t.arrival_time >='" + searchInfo.getStartTime() + "' and t.treatment_unit='"+classification+"' group by t1.id) b) as volume, " +
                " (select SUM(t.sectionerformance) from (select t.section_performance as sectionerformance from reservations_infosc t1 left join "+tableName2+" t2 on t1.id=t2.reservations_id " +
                " left join "+tableName3+" t on t2.id=t.customer_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' and t.recharge_time >= '" + searchInfo.getStartTime() + "'  and t2.treatment_unit='"+classification+"' group by t.id) t) as channelPerformance from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getsqlDepartmentsTwo(SearchMarketReportTwoInfo searchInfo, String classification, String condition) {
        String getabbreviationsql = "select id, abbreviation,Simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead="";
        if(dept==null){
            tableHead= ShiroKit.getUser().getAbbreviation();
        }else{
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select '"+classification+"' as department , " +
                " (select count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime() + "' and t.filing_time <='" + searchInfo.getEndTime() + "' and t.appointment_pay_service='"+classification+"' and t.outpatient_name='" + searchInfo.getArrivalStore() + "' group by t.id) t) AS filingNumber, " +
                " (select count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime() + "' and t.appointment_time <='" + searchInfo.getEndTime() + "' and t.appointment_pay_service='"+classification+"' and t.outpatient_name='" + searchInfo.getArrivalStore() + "' group by t.id) t) AS appointmentNumber," +
                " (select count(1) from (select t.id from reservations_infosc t left join "+tableName2+" t1 on t.id=t1.reservations_id where t.first_visit_date>='" + searchInfo.getStartTime() + " ' and t.first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + searchInfo.getArrivalStore() + "'  and t1.treatment_unit='"+classification+"' group by t.id) t) as customerxkNumber," +
                " (select count(1) from (select t1.id from  reservations_infosc t left join "+tableName2+" t1 on t.id=t1.reservations_id where t1.arrival_status!=1 and t1.arrival_status!=8 and t1.arrival_status!=9 and t1.arrival_time <='" + searchInfo.getEndTime() + "' and t1.arrival_time >='" + searchInfo.getStartTime() + "' and t1.treatment_unit='"+classification+"' group by t.id) b) as customerNumber, " +
                " (select count(1) from (select t1.id from reservations_infosc t left join "+tableName2+" t1 on t.id=t1.reservations_id where t1.arrival_time <='" + searchInfo.getEndTime() + "'  and t1.isdeal_status='已成交' and t1.arrival_time >='" + searchInfo.getStartTime() + "' and t1.treatment_unit='"+classification+"' group by t.id) b) as volume, " +
                " (select SUM(t.sectionerformance) from (select t.section_performance as sectionerformance from reservations_infosc t1  left join "+tableName2+" t2 on t1.id=t2.reservations_id " +
                " left join "+tableName3+" t on t2.id=t.customer_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' and t.recharge_time >= '" + searchInfo.getStartTime() + "'  and t2.treatment_unit='"+classification+"' group by t.id) t) as channelPerformance   from dual ";
        return sql;
    }

    public String getTotleSqlDepartmentTwo(SearchMarketReportTwoInfo searchInfo) {
        String getabbreviationsql = "select id, abbreviation,Simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if(dept==null){
            tableHead= ShiroKit.getUser().getAbbreviation();
        }else{
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = "  select '合计' as department , " +
                " (select count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime() + "' and t.filing_time <='" + searchInfo.getEndTime() + "'  and t.outpatient_name='" + searchInfo.getArrivalStore() + "' group by t.id) t) AS filingNumber, (select count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime() + "' and t.appointment_time <='" + searchInfo.getEndTime() + "'  and t.outpatient_name='" + searchInfo.getArrivalStore() + "' group by t.id) t) AS appointmentNumber," +
                " (select count(1) from (select t.id from  reservations_infosc t left join "+tableName2+" t1  on t.id=t1.reservations_id where t.first_visit_date>='" + searchInfo.getStartTime() + " ' and t.first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + searchInfo.getArrivalStore() + "' group by t.id) t) as customerxkNumber," +
                " (select count(1) from (select t1.id from reservations_infosc t left join  "+tableName2+" t1 on t.id=t1.reservations_id where t1.arrival_status!=1 and t1.arrival_status!=8 and t1.arrival_status!=9 and t1.arrival_time <='" + searchInfo.getEndTime() + "' and t1.arrival_time >='" + searchInfo.getStartTime() + "' group by t.id) t) as customerNumber," +
                " (select count(1) from (select t1.id from reservations_infosc t left join "+tableName2+" t1 on t.id=t1.reservations_id where t1.arrival_time <='" + searchInfo.getEndTime() + "' and t1.isdeal_status='已成交' and t1.arrival_time >='" + searchInfo.getStartTime() + "' group by t.id) b) as volume," +
                " (select SUM(t.sectionerformance) from (select t.section_performance as sectionerformance from reservations_infosc t1 " +
                " left join "+tableName3+" t on t1.id=t.reservations_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' and t.recharge_time >= '" + searchInfo.getStartTime() + "' group by t.id) t) as channelPerformance from dual ";
        return sql;
    }

    public String getsqlMarketPersonnel(SearchMarketReportTwoInfo searchInfo, String channel, String classification,
                                        int i, List<SearchMarketReportInfo> list1, String simplename,
                                        String condition, String condition1, String condition2, String condition3) {
        String getabbreviationsql = "select id, abbreviation,Simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = "   select " + channel + " , " +
                "(select count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime()
                + "' and t.filing_time <='" + searchInfo.getEndTime() + "' and archiving='" + classification + "' and t.outpatient_name='" + simplename + "'" +
                " " + condition + " group by t.id) t) AS filingNumber," +

                "(select count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime()
                + "' and t.appointment_time <='" + searchInfo.getEndTime() + "' and t.archiving='" + classification + "' and t.outpatient_name='" + simplename + "' " +
                " " + condition + " group by t.id) t) AS appointmentNumber," +

                "(select count(1) from (select t.id from reservations_infosc t left join " + tableName2 + " t1 on t.id=t1.reservations_id where t.first_visit_date>='" + searchInfo.getStartTime()
                + "' and t.first_visit_date <='" + searchInfo.getEndTime() + "' and t.archiving='" + classification + "' and t.arrival_store='" + simplename + "' " +
                " " + condition1 + " group by t.id) t) as customerxkNumber," +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join  " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving='" + classification + "' " + condition3 + " group by t.reservations_id) b) as customerNumber, " +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join  " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.isdeal_status='已成交' " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving='" + classification + "' " + condition3 + " group by t.reservations_id) b) as volume," +

                " (select SUM(t.sectionerformance) from (select t.section_performance as sectionerformance from reservations_infosc t1 " +
                " left join "+tableName2+" t2 on t1.id=t2.reservations_id " +
                " left join "+tableName3+" t on t2.id=t.customer_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' and " +
                " t.recharge_time >= '" + searchInfo.getStartTime() + "' " +
                " and t1.archiving='" + classification + "' " + condition2 + " group by t.id) t) as channelPerformance  " +

                " from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getsqlHospitalWide(SearchMarketReportTwoInfo searchInfo, String channel,  String simplename,
                                     String condition, String condition1, String condition2, String condition3) {
        String getabbreviationsql = "select id, abbreviation,Simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select " + channel + " , " +
                "(select  count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime()
                + "' and filing_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + simplename + "'" +
                " " + condition + " group by t.id) t) AS filingNumber," +

                "(select  count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime()
                + "' and appointment_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + simplename + "' " +
                " " + condition + " group by t.id) t) AS appointmentNumber," +

                "(select  count(1) from (select t.id from reservations_infosc t left join " + tableName2 + " t1 on t.id=t1.reservations_id where t.first_visit_date>='" + searchInfo.getStartTime()
                + " ' and t.first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + simplename + "' " +
                " " + condition1 + " group by t.id) t) as customerxkNumber," +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join  " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving is not null " + condition3 + " group by t.reservations_id) b) as customerNumber, " +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join  " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.isdeal_status='已成交' " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving is not null " + condition3 + " group by t.reservations_id) b) as volume," +

                " (select SUM(t.sectionerformance) from (select t.section_performance as sectionerformance from reservations_infosc t1 " +
                " left join "+tableName2+" t2 on t1.id=t2.reservations_id " +
                " left join "+tableName3+" t on t2.id=t.customer_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' and" +
                " t.recharge_time >= '" + searchInfo.getStartTime() + "' " +
                " "+condition2+" group by t.id) t) as channelPerformance " +
                " from dual ";
        return sql;
    }

    public String getsqlDepartment(SearchMarketReportTwoInfo searchInfo, String channel, String condition,
                                   int i, List<SearchMarketReportTwoInfo> list1, String abbreviation, String simplename,
                                   String condition1,String condition2,String condition3,String condition4,String condition5) {
        String getabbreviationsql = "select id, abbreviation,Simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = "   select " + channel + " , " +
                "(select  count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime()
                + "' and t.filing_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + simplename + "' "+condition+" "+condition1+" group by t.id) t) AS filingNumber," +
                "(select  count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime()
                + "' and t.appointment_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + simplename + "' "+condition+" "+condition1+" group by t.id) t) AS appointmentNumber," +
                "(select  count(1) from (select t.id from reservations_infosc t where t.first_visit_date>='" + searchInfo.getStartTime()
                + " ' and t.first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + simplename + "' "+condition+" "+condition1+" group by t.id)t) as customerxkNumber," +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join  " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " "+condition+" "+condition2+" group by t.reservations_id) b) as customerNumber, " +

                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join  " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.isdeal_status='已成交' " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " "+condition+" "+condition2+" group by t.reservations_id) b) as volume," +

                " (select SUM(t.sectionerformance) from (select t.section_performance as sectionerformance from reservations_infosc t1 " +
                " left join "+tableName2+" t2 on t1.id=t2.reservations_id " +
                " left join "+tableName3+" t on t2.id=t.customer_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' and t.recharge_time >= '" + searchInfo.getStartTime() + "' " +
                " "+condition5+" "+condition3+" group by t.id) t) as channelPerformance  " +

                " from dual ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getTotleDepartmentSql(SearchMarketReportTwoInfo searchInfo, String channel, String condition, String abbreviation,
                                        String condition1,String condition2,String condition3,String condition4,String conditions1) {
        String getabbreviationsql = "select id, abbreviation,Simplename from dept t where t.simplename='" + searchInfo.getArrivalStore() + "'";
        Dept dept = sqlMapper.selectOne(getabbreviationsql, Dept.class);
        String tableHead = "";
        if (dept == null) {
            tableHead = ShiroKit.getUser().getAbbreviation();
        } else {
            tableHead = dept.getAbbreviation();
        }
        String tableName2 = tableHead + "customer_info";
        String tableName3 = tableHead + "recharge_info";
        String sql = " select " + channel + " , " +
                "(select  count(1) from (select t.id from reservations_infosc t where t.filing_time>='" + searchInfo.getStartTime()
                + "' and filing_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + dept.getSimplename() + "' "+condition+" "+condition1+" group by t.id)t) AS filingNumber," +
                "(select  count(1) from (select t.id from reservations_infosc t where t.appointment_time>='" + searchInfo.getStartTime()
                + "' and appointment_time <='" + searchInfo.getEndTime() + "' and t.outpatient_name='" + dept.getSimplename() + "' "+condition+" "+condition1+" group by t.id)t) AS appointmentNumber," +
                "(select  count(1) from (select t.id from reservations_infosc t where t.first_visit_date>='" + searchInfo.getStartTime()
                + " ' and first_visit_date <='" + searchInfo.getEndTime() + "' and t.arrival_store='" + dept.getSimplename() + "' "+condition+" "+condition1+" group by t.id) t)as customerxkNumber," +
                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join  " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.arrival_status!=1 and t.arrival_status!=8 and t.arrival_status!=9 " +
                " and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                " and t.scarchiving is not null "+condition+" "+condition2+" group by t.reservations_id) b) as customerNumber, " +
                " (select count(1) from (select t.id from reservations_infosc t1 " +
                " left join  " + tableName2 + " t on (t1.id=t.reservations_id) " +
                " where t.isdeal_status='已成交' " +
                " and t.scarchiving is not null and t.arrival_time <='" + searchInfo.getEndTime() + "' and t.arrival_time >='" + searchInfo.getStartTime() + "' " +
                "  "+condition+" "+condition2+" group by t.reservations_id) b) as volume," +
                " (select SUM(t.sectionerformance) from (select t.section_performance as sectionerformance from reservations_infosc t1 " +
                " left join "+tableName2+" t2 on t1.id=t2.reservations_id " +
                " left join "+tableName3+" t on t2.id=t.customer_id where t.recharge_time <= '" + searchInfo.getEndTime() + "' and t.recharge_time >= '" + searchInfo.getStartTime() + "' " +
                " "+conditions1+" "+condition3+" group by t.id) t) as channelPerformance  " +
                " from dual ";
        return sql;
    }
}